USE [BMIG_MENSUAL_POST_CADENA]

 -- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = 'WASP_M0010_INGRESO_PRODUCCION'
	   AND 	  type = 'P') 
	DROP PROCEDURE WASP_M0010_INGRESO_PRODUCCION
GO

CREATE PROCEDURE [dbo].[WASP_M0010_INGRESO_PRODUCCION](@FECHA_INICIO CHAR(8), @FECHA_TERMINO AS CHAR(8)) AS
begin 

    SELECT  'D' AS TipoRegistro
          , NULL AS PeriodoPro
          , NULL AS FechaEnvio
          , 1 AS NroCorrelativoArchivo
          , 1 AS TipoMovimiento
          , G.COD_SEg AS NroProducto
          , NULL AS NroPoliza
          , a.aosuc AS CODAGEN
          , a.aooper AS NroCredito
          , SUBSTRING(c.pfndoc, 1, LEN(c.pfndoc) - 1) AS RutAsegurado
          , SUBSTRING(c.pfndoc, LEN(c.pfndoc), 1) AS [DV Rut Asegurado]
          , CAST(RTRIM(LTRIM(c.pfape1)) + ' ' + RTRIM(LTRIM(c.pfape2)) + ' ' + RTRIM(LTRIM(c.pfnom1)) + ' ' + RTRIM(LTRIM(c.pfnom2)) AS varchar(50)) AS [Nombre Asegurado]
          , e.docall + CAST(e.donro AS varchar) AS DireccionAsegurado
          , f.tel1 AS TelefonoAsegurado
          , d.z07310COM AS CodigoComuna
          , NULL AS DescripcionComuna
          , NULL AS CodigoCiudad
          , NULL AS DescripcionCuidad
          , d.z07339REG AS CodigoRegion
          , NULL AS DescripcionRegion
          , CONVERT(varchar, a.aofval, 112) AS VigenciaDesde
          , CONVERT(varchar, DATEADD(day, - 1, DATEADD(year, 1, a.aofval)), 112) AS VigenciaHasta
          , NULL AS CuotasPactadas
          , NULL AS PrimaNetaCaja
          , g.val_seg AS PrimaBrutaCaja
          , g.val_seg / 1.19 AS PrimaNetaPesos
          , g.val_seg AS PrimaBrutaPesos
    into	dbo.ing_produccion_temp      
    FROM  dbo.fsd010 AS a WITH (nolock) 
          LEFT OUTER JOIN dbo.fsd002 AS c WITH (nolock) ON a.aocta = SUBSTRING(c.pfndoc, 1, LEN(pfndoc) - 1) 
          LEFT OUTER JOIN dbo.z07331 AS d WITH (nolock) ON a.aocta = d.z07331cta 
          LEFT OUTER JOIN dbo.fsd006 AS e WITH (nolock) ON a.aocta = e.ctnro 
          LEFT OUTER JOIN dbo.dbo.ingreso_produccion_numero_telefono AS f WITH (nolock) ON a.aocta = f.ctnro 
          CROSS JOIN dbo.ingreso_produccion_seguro_asisten AS g WITH (nolock) 
          CROSS JOIN dbo.JT73109 AS h WITH (nolock)
    WHERE (a.aooper <> 99999999) 
          AND (a.aocta <> 999999999) 
          AND (a.aosbop = 0) 
          AND (pfndoc <> '') 
          AND (h.JT73109FAP BETWEEN @FECHA_INICIO AND @FECHA_TERMINO) 
          AND (NOT EXISTS(SELECT *
                          FROM          dbo.as_desistidos AS anu
                          WHERE      (a.aocta = anu.hcta) AND (a.aooper = anu.hoper) AND (a.aosuc = anu.hsucur))) 
          AND (c.pfndoc <> '*********8')
END          
                                
